from typing import List, Tuple, Dict
from datetime import datetime

class DataManager:
    async def get_unique_user_ids_and_sec_uids(self, platform: str, limit: int = None) -> List[Tuple[str, str]]:
        """获取唯一的用户ID和sec_uid对列表"""
        async with self.pool.acquire() as conn:
            async with conn.cursor() as cursor:
                query = """
                    SELECT DISTINCT user_id, sec_uid
                    FROM dy_creator
                    WHERE platform = %s
                    AND user_id IS NOT NULL
                    AND sec_uid IS NOT NULL
                """
                if limit:
                    query += " LIMIT %s"
                    await cursor.execute(query, (platform, limit))
                else:
                    await cursor.execute(query, (platform,))
                return await cursor.fetchall()

    async def get_fans_ids(self, platform: str, user_id: str, limit: int = None) -> List[Tuple[str, str]]:
        """获取指定用户的粉丝ID和sec_uid列表"""
        async with self.pool.acquire() as conn:
            async with conn.cursor() as cursor:
                query = """
                    SELECT DISTINCT f.fan_id, c.sec_uid
                    FROM dy_fans f
                    LEFT JOIN dy_creator c ON f.fan_id = c.user_id
                    WHERE f.platform = %s
                    AND f.user_id = %s
                    AND c.sec_uid IS NOT NULL
                """
                if limit:
                    query += " LIMIT %s"
                    await cursor.execute(query, (platform, user_id, limit))
                else:
                    await cursor.execute(query, (platform, user_id))
                return await cursor.fetchall()

    async def get_topic_user_ids(self, platform: str, topic: str, limit: int = None) -> List[Tuple[str, str]]:
        """获取在指定话题下发布过内容的用户ID和sec_uid列表"""
        async with self.pool.acquire() as conn:
            async with conn.cursor() as cursor:
                query = """
                    SELECT DISTINCT n.user_id, c.sec_uid
                    FROM dy_notes n
                    LEFT JOIN dy_creator c ON n.user_id = c.user_id
                    WHERE n.platform = %s
                    AND n.topic LIKE %s
                    AND c.sec_uid IS NOT NULL
                """
                if limit:
                    query += " LIMIT %s"
                    await cursor.execute(query, (platform, f"%{topic}%", limit))
                else:
                    await cursor.execute(query, (platform, f"%{topic}%"))
                return await cursor.fetchall()

    async def get_user_notes(self, platform: str, user_id: str, limit: int = None) -> List[Dict]:
        """获取用户的主题列表"""
        async with self.pool.acquire() as conn:
            async with conn.cursor() as cursor:
                query = """
                    SELECT *
                    FROM dy_notes
                    WHERE platform = %s
                    AND user_id = %s
                    ORDER BY create_time DESC
                """
                if limit:
                    query += " LIMIT %s"
                    await cursor.execute(query, (platform, user_id, limit))
                else:
                    await cursor.execute(query, (platform, user_id))
                return await cursor.fetchall()

    async def get_user_comments(self, platform: str, user_id: str, limit: int = None) -> List[Dict]:
        """获取用户的评论列表"""
        async with self.pool.acquire() as conn:
            async with conn.cursor() as cursor:
                query = """
                    SELECT *
                    FROM dy_comments
                    WHERE platform = %s
                    AND user_id = %s
                    ORDER BY create_time DESC
                """
                if limit:
                    query += " LIMIT %s"
                    await cursor.execute(query, (platform, user_id, limit))
                else:
                    await cursor.execute(query, (platform, user_id))
                return await cursor.fetchall()

    async def get_user_stats(self, platform: str, user_id: str) -> Dict:
        """获取用户的统计数据"""
        async with self.pool.acquire() as conn:
            async with conn.cursor() as cursor:
                # 获取用户发布的主题数量
                await cursor.execute("""
                    SELECT COUNT(*) as notes_count,
                           SUM(liked_count) as total_likes,
                           SUM(comment_count) as total_comments
                    FROM dy_notes
                    WHERE platform = %s AND user_id = %s
                """, (platform, user_id))
                notes_stats = await cursor.fetchone()
                
                # 获取用户发表的评论数量
                await cursor.execute("""
                    SELECT COUNT(*) as comments_count
                    FROM dy_comments
                    WHERE platform = %s AND user_id = %s
                """, (platform, user_id))
                comments_count = (await cursor.fetchone())[0]
                
                # 获取用户的粉丝和关注数量
                await cursor.execute("""
                    SELECT fans_count, following_count
                    FROM dy_creator
                    WHERE platform = %s AND user_id = %s
                """, (platform, user_id))
                relationship_stats = await cursor.fetchone()
                
                return {
                    "notes_count": notes_stats[0],
                    "total_likes": notes_stats[1],
                    "total_comments": notes_stats[2],
                    "comments_count": comments_count,
                    "fans_count": relationship_stats[0] if relationship_stats else 0,
                    "following_count": relationship_stats[1] if relationship_stats else 0
                }

    async def get_filtered_data(self, 
                              platform: str,
                              data_type: str,
                              keyword: str = None,
                              start_date: datetime = None,
                              end_date: datetime = None,
                              limit: int = 100) -> List[Dict]:
        """获取筛选后的数据"""
        async with self.pool.acquire() as conn:
            async with conn.cursor() as cursor:
                conditions = ["platform = %s"]
                params = [platform]
                
                if keyword:
                    if data_type == "用户基础信息":
                        conditions.append("(nickname LIKE %s OR signature LIKE %s)")
                        params.extend([f"%{keyword}%", f"%{keyword}%"])
                    elif data_type == "用户主题数据":
                        conditions.append("(title LIKE %s OR content LIKE %s)")
                        params.extend([f"%{keyword}%", f"%{keyword}%"])
                    elif data_type == "主题评论数据":
                        conditions.append("content LIKE %s")
                        params.append(f"%{keyword}%")
                
                if start_date:
                    conditions.append("create_time >= %s")
                    params.append(start_date)
                if end_date:
                    conditions.append("create_time <= %s")
                    params.append(end_date)
                
                where_clause = " AND ".join(conditions)
                
                if data_type == "用户基础信息":
                    table = "dy_creator"
                    fields = ["user_id", "sec_uid", "nickname", "signature", 
                             "fans_count", "following_count", "create_time"]
                elif data_type == "用户主题数据":
                    table = "dy_notes"
                    fields = ["content_id", "user_id", "title", "content", 
                             "liked_count", "comment_count", "create_time"]
                elif data_type == "主题评论数据":
                    table = "dy_comments"
                    fields = ["comment_id", "content_id", "user_id", "content",
                             "liked_count", "create_time"]
                elif data_type == "用户关系数据":
                    table = "dy_fans"
                    fields = ["user_id", "fan_id", "create_time"]
                
                query = f"""
                    SELECT {', '.join(fields)}
                    FROM {table}
                    WHERE {where_clause}
                    ORDER BY create_time DESC
                    LIMIT %s
                """
                params.append(limit)
                
                await cursor.execute(query, params)
                rows = await cursor.fetchall()
                
                return [dict(zip(fields, row)) for row in rows] 